package com.owen.he.jee.utils;

import java.awt.Color;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.springframework.util.Assert;

/**
 * 
 * @author owen.he
 *
 */
public class ExportExcel {
	
	private static final Logger logger = Logger.getLogger(ExportExcel.class);
	
	public static final int MAX_ROW_PER_SHEET = 65536;
	/** 初始列 */
	private static final int DEAULT_START_COLUMN_NUMBER = 0;
	
	/**excel对象 */
	private static SXSSFWorkbook workbook;
	/**Excel名字*/
	private String excelName;
	/** 数据集合 */
	private List<Map> excelData;
	/** 单元格属性 */
	private List<ExcelRelation> excelRelations;
	/** 单元格的样式  */
	private HSSFCellStyle cellStyle;
	
	public static SXSSFWorkbook getWorkbook() {
		return workbook;
	}
	public static void setWorkbook(SXSSFWorkbook workbook) {
		ExportExcel.workbook = workbook;
	}
	public String getExcelName() {
		return excelName;
	}
	public void setExcelName(String excelName) {
		this.excelName = excelName;
	}
	public List<Map> getExcelData() {
		return excelData;
	}
	public void setExcelData(List<Map> excelData) {
		this.excelData = excelData;
	}
	public List<ExcelRelation> getExcelRelations() {
		return excelRelations;
	}
	public void setExcelRelations(List<ExcelRelation> excelRelations) {
		this.excelRelations = excelRelations;
	}
	public HSSFCellStyle getCellStyle() {
		return cellStyle;
	}
	public void setCellStyle(HSSFCellStyle cellStyle) {
		this.cellStyle = cellStyle;
	}
	/** ----------------数据渲染--------------- */
	
	public void createExportExcel() {
		init();
		//创建一个excel工作表
		Sheet sheet = workbook.createSheet(excelName);
		/** 自增控制excle行 */
        AtomicInteger excelRowNum = new AtomicInteger(0);
        //1.将标题写到excel临时文件中
        int andIncrement = excelRowNum.getAndIncrement();
		doExportExcelHeader(sheet, andIncrement);
		//2.导出数据到临时文件中
        if(excelData!=null && excelData.size()>0) {
        	doExportExcelBody(sheet, excelRowNum);
        }
	}
	
	/**
	 * 根据fieldName设置值
	 * @param sheet
	 * @param excelRowNum
	 */
	private void doExportExcelBody(Sheet sheet, AtomicInteger excelRowNum) {
		final Iterator datas = excelData.iterator();
		while (datas.hasNext()) {
			Map<String,Object> dataMap = (Map<String, Object>) datas.next();
			Row row = sheet.createRow(excelRowNum.getAndIncrement());
			int colNum = DEAULT_START_COLUMN_NUMBER;//列的索引号
			Cell cell;//单元格
			for (ExcelRelation excel : excelRelations) {
				//单元格赋值
				String field = excel.getField();
				Object value = dataMap.get(field);
				int column = colNum++;
				cell = row.createCell(column);
				if(value != null) {
					/**此处可根据需要设置输出的值，未实现*/
					if (value instanceof String) {
                        if (StringUtils.isBlank((String) value)) {
                            cell.setCellValue("-");
                        } else {
                            cell.setCellValue((String) value);
                        }
                    } else {
                        cell.setCellValue(value.toString());
                    }
				}else {
					cell.setCellValue("-");
				}
				//单元格样式
				excel.setRequired(false);//必填项内容不要
				setCellStyle(sheet, cell, excel, column);
			}
		}
	}
	
	
	/**
	 * 判断初始化的参数是否有值
	 */
	private void init() {
		if(workbook==null) {
			workbook = new SXSSFWorkbook(-1);
		}
		Assert.notNull(excelName, "导出的excel名字不能为空！");
		Assert.isTrue(excelRelations!=null && excelRelations.size()>0,"导出excel对象格式不正确!");
	}
	/**
	 * 创建一行表头的工作表表头
	 * @param sheet 工作表
	 * @param rowNum 列的索引号
	 */
	private void doExportExcelHeader(Sheet sheet, int rowNum) {
		/** 初始化行&单元格 */
		Row row = sheet.createRow(rowNum);
		int colNum = DEAULT_START_COLUMN_NUMBER;//列的索引号
		Cell cell;//单元格
		for (ExcelRelation excel : excelRelations) {
			//创建单元格并赋值
			String name = excel.getName();
			int column = colNum++;
			cell = row.createCell(column);
			cell.setCellValue(name);
			setCellStyle(sheet, cell, excel, column);
		}
	}
	
	
	/**
	 * 设置单元格的样式
	 * @param sheet
	 * @param cell
	 * @param excel
	 * @param column
	 */
	private void setCellStyle(Sheet sheet, Cell cell, ExcelRelation excel,
			int column) {
		//单元格样式
		XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.createCellStyle();
		//必填项
		cellIsRequired(cellStyle, excel.isRequired());
		//单元格对齐方式
		cellAlign(cellStyle, excel.getAlign());
		//单元格颜色
		cellForegroundColor(cellStyle, excel.getColor());
		//宽高
		cellColumnWidth(sheet, column, excel.getWidth());
		//注释
		cellAnnotate(sheet, cell, excel.getAnnotate());
		cell.setCellStyle(cellStyle);
	}
	
	/**
	 * 设置批注
	 * @param sheet 工作表
	 * @param cell
	 * @param mark
	 */
	private void cellAnnotate(Sheet sheet, Cell cell, String mark) {
		if(StringUtils.isNotBlank(mark)) {
			XSSFDrawing p = (XSSFDrawing) sheet.createDrawingPatriarch();
			XSSFComment comment = p.createCellComment(new HSSFClientAnchor(0,0,0,0,(short)3,3,(short)5,6));
	        // 输入批注信息
	        comment.setString(new XSSFRichTextString(mark));
	        // 将批注添加到单元格对象中
	        cell.setCellComment(comment);
		}
	}
	/**
	 * 设置单元格背景色
	 * @param cellStyle
	 * @param color
	 */
	private void cellForegroundColor(XSSFCellStyle cellStyle, String color) {
		if(StringUtils.isNotBlank(color)) {
			XSSFColor setBorderColor = setBorderColor(color);
			cellStyle.setFillForegroundColor(setBorderColor);
		}
		
	}
	/**
	 * 解析颜色
	 * @param color
	 * @return
	 */
	private XSSFColor setBorderColor(String color) {
		XSSFColor borderColor = null;
		if(StringUtils.isNoneBlank(color)) {
			String[] borderColorRGB = color.split(":");
			if(borderColorRGB.length >= 3 ) {
				borderColor = new XSSFColor(new Color(Integer.parseInt(borderColorRGB[0]),
						Integer.parseInt(borderColorRGB[1]), Integer.parseInt(borderColorRGB[2])));
			}
		}
		return borderColor;
	}
	
	/**
	 * 设置列宽
	 * @param sheet
	 * @param column
	 * @param width
	 */
	private void cellColumnWidth(Sheet sheet, int column, int width) {
		if(width>0) {
			sheet.setColumnWidth(column, width);
		}
	}
	/**
	 * 设置单元格对齐方式 默认为居中
	 * @param cellStyle
	 * @param align
	 */
	private void cellAlign(XSSFCellStyle cellStyle, String align) {
		short alignStyle = HSSFCellStyle.ALIGN_CENTER;
		if(StringUtils.isNotBlank(align)){
    		if(align.equalsIgnoreCase("left")){
    			alignStyle = HSSFCellStyle.ALIGN_LEFT;
    		}else if(align.equalsIgnoreCase("right")){
    			alignStyle = HSSFCellStyle.ALIGN_RIGHT;
    		}else if(align.equalsIgnoreCase("general")){
    			alignStyle = HSSFCellStyle.ALIGN_GENERAL;
    		}else if(align.equalsIgnoreCase("fill")){
    			alignStyle = HSSFCellStyle.ALIGN_FILL;
    		}else if(align.equalsIgnoreCase("justify")){
    			alignStyle = HSSFCellStyle.ALIGN_JUSTIFY;
    		}
    	}
		cellStyle.setAlignment(alignStyle);
	}
	/**
	 * 设置必填背景颜色
	 * @param cellStyle
	 * @param required
	 */
	private void cellIsRequired(XSSFCellStyle cellStyle, boolean required) {
		if(required) {
			cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
			cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
		}
	}
	
	
	
}
